package service;
import java.io.File;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.CellType;

import com.jfinal.kit.Kv;
import com.jfinal.kit.Ret;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import common.kit.ExcelKit;
import common.kit.PageSql;
import enums.CompanyStatus;
import enums.WxuserRole;
import model.Company;
import model.Servicer;
import model.ServicerCompany;
import model.Station;
import model.Wxuser;

public class CompanyService extends BaseService {

	public Page<Record> getApiPage(Kv kv , Integer pageNumber , Integer pageSize) {
		kv.set("tc.isDelete",0);
		String select = " tc.id AS companyId,tc.name,tc.address,tc.latitude,tc.longitude,tc.contacts,tc.mobile,tc.industry,tc.status " ;
	    PageSql pageSql = PageSql.bySelect( kv , select ) ;
	    pageSql.from(" t_company tc ") ;
	    pageSql.join("t_servicer_company tsc", " tsc.companyId = tc.id ");
	    pageSql.join("t_servicer tsr", " tsc.serviserId = tsr.id ");
	    pageSql.setLikeCondition("tc.name") ; //企业名称
	    pageSql.setLikeCondition("tsr.name") ; //监管企业名称
		pageSql.setEqualCondition("tc.isDelete") ;
		pageSql.setLikeCondition("tc.name") ;
		pageSql.orderBy(" tc.createTime DESC ");
		pageSql.groupBy("tc.id");
		Page<Record> page = pageSql.paginate( pageNumber , pageSize) ;
		List<Record> list = page.getList();
		for (Record record : list) {
			List<Servicer> servicers = ServicerCompany.dao.findByCompanyId(record.getLong("companyId"));
			record.set("serviers", getServicersNames(servicers) );
			record.set("statusStr", CompanyStatus.getTitleByValue(record, "status")) ;
		}
		return page ;
	}
	
	public String getServicersNames(List<Servicer> list) {
		StringBuffer sBuffer = new StringBuffer();
		for (Servicer servicer : list) {
			sBuffer.append(sBuffer.length() > 0 ? "、" : "").append(servicer.getName());
		}
		return sBuffer.toString() ;
	}
	
	public Page<Record> getPage(Kv kv , Integer pageNumber , Integer pageSize) {
	    kv.set("tc.isDelete" , 0 );
		String select = " tc.*" ;
	    PageSql pageSql = PageSql.bySelect( kv , select ) ;
	    pageSql.from(" t_company tc ") ;
	    pageSql.join(" t_servicer_company tsc ", " tsc.companyId = tc.id ");
		pageSql.setLikeCondition("tc.name") ; //企业名称
		pageSql.setLikeCondition("tc.address") ; //企业地址
		pageSql.setLikeCondition("tc.mobile") ; //手机号码
		pageSql.setEqualCondition("tc.isDelete") ; //是否删除
		pageSql.setEqualCondition("tsc.serviserId") ;
	    pageSql.orderBy(" tc.createTime DESC ");
	    pageSql.groupBy("tc.id");
	    Page<Record> page = pageSql.paginate( pageNumber , pageSize) ;
		List<Record> list = page.getList();
		for (Record record : list) {
			List<Servicer> servicers = ServicerCompany.dao.findByCompanyId(record.getLong("id"));
			record.set("list", servicers);
		}
		return page ;
	}

	public Ret delete(Long id) {
        Company company = Company.dao.findById(id);
        if(null == company){
            return fail();
        }
        company.setIsDelete(true);
        company.update() ;
        Wxuser.dao.deleteCompany(company);
        ServicerCompany.dao.deleteByCompany(company);
        return ok() ;
    }

	public Ret save(Company company) {
		company.setIsDelete(false);
	    Company ex = Company.dao.findFirstByName( company.getName() );
	    if(null == ex){
	        company.save();
	    }else{
	    	company.setId(ex.getId());
	        company.update() ;
	    }
	    Wxuser.dao.saveForCompany(company.getMobile(), WxuserRole.getCompany() , company.getId() );
	    return ok() ;
	}
	
	public int saveByName(Company company) {
		if (StrKit.isBlank(company.getName())) {
			return -1 ;
		}
		Company ex = Company.dao.findFirstByName(company.getName());
		company.setIsDelete(false);
	    if(null == ex){
	        company.save();
	        Wxuser.dao.saveForCompany(company.getMobile(), WxuserRole.getCompany(), company.getId() );
	        return 1 ;
	    }
	    company.setId(ex.getId());
        company.update() ;
        Wxuser.dao.saveForCompany(company.getMobile(), WxuserRole.getCompany(), company.getId() );
        return 0 ;
	}
	
	public Ret importExcel(File file){
		ExcelKit excelKit = new ExcelKit(file);
		HSSFSheet hs = excelKit.getHSSheet() ;
	    int first = 1 ;
	    int skipCount = 0  , saveCount = 0 , updateCount = 0 ;
	    for (int i = first; i <= hs.getLastRowNum() ; i++) {
		    HSSFRow row = hs.getRow(i);
		    int firstCellNum = row.getFirstCellNum();//获取所在行的第一个行号
		    int lastCellNum = row.getLastCellNum();//获取所在行的最后一个行号
		    String[] keys = {"name","industry","address","latitude","longitude","contacts","mobile"};
		    Company company = new Company();
			for (int j = firstCellNum; j <lastCellNum; j++) {
				HSSFCell cell = row.getCell(j);
				cell.setCellType(CellType.STRING);
				String value = cell.getStringCellValue() ;
				company.set(keys[j], value);
			}
			int count = this.saveByName(company);
			if (count == -1) {
				skipCount ++ ;
			}
			if (count == 0) {
				updateCount ++ ;
			}
			if (count == 1) {
				saveCount ++ ;
			}
	    }
	    file.delete();
	    excelKit.close();
	    return Ret.create("saveCount",saveCount).set("updateCount" , updateCount).set("skipCount" , skipCount);
	}
	
	public File exportExcel(Kv kv) {
		ExcelKit excelKit = new ExcelKit();
		HSSFSheet sheet = excelKit.getHSSheet() ;
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = null ;
		String titleString = "企业名称,所属行业,企业地址,经度,纬度	,联系人,联系电话";
		String[] titles = titleString.split(",");
		for (int i = 0; i < titles.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(titles[i]);
		}
		int rowNum = 1 ;
		int pageNumber = 0 ;
		boolean lastPage = false ;
		do {
			pageNumber ++ ;
			Page<Record> page = this.getPage(kv, pageNumber, 200);
			lastPage = page.isLastPage();
			List<Record> list = page.getList() ;
			for (Record record : list) {
				row = sheet.createRow(rowNum);
				String[] arr = { record.getStr("name") , record.getStr("industry") , 
						record.getStr("address") , record.getBigDecimal("longitude").toString() , record.getBigDecimal("latitude").toString(),
						record.getStr("contacts") , record.getStr("mobile") };
				for (int i = 0; i < titles.length; i++) {
					cell = row.createCell(i);
					cell.setCellValue(arr[i]);
				}
				rowNum ++ ;
			}
		} while (!lastPage);
		return excelKit.writeExcel();
	}
	
	public Ret getMarkers() {
		List<Record> list = Company.dao.findMarkers();
		for (Record record : list) {
			record.set("id", "company_" + record.getLong("id") );
			record.set("iconPath", "/static/company.png");
			record.set("width",30);
			record.set("height", 30);
		}
		List<Record> stations = Station.dao.findMarkers();
		for (Record record : stations) {
			record.set("id", "station_" + record.getLong("id") );
			record.set("iconPath", "/static/station.png");
			record.set("width",30);
			record.set("height", 30);
			list.add(record);
		}
		return Ret.create("list" , list);
	}

	public Ret search(String name) {
		if (StrKit.isBlank(name)) {
			return Ret.create();
		}
		List<Company> list = Company.dao.findByName(name);
		return Ret.create("list" , list);
    }
}
